/*
 * Created on 2004-02-25
 */
package org.opencube.oms.test;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import oracle.sql.BLOB;
import oracle.sql.CLOB;

import org.opencube.util.Util;

import info.fingo.db.Column;
import info.fingo.db.ForeignKey;

/**
 * @author <a href="mailto:maciek@fingo.pl">FINGO - Maciej Mroczko</a>
 * TODO: comment
 */
public class DBHash
{
	/* The database connection */
	private Connection conn = null;
	/* The column names to be ignored while counting */
	private ArrayList ignoredColumns = new ArrayList();
	/* The primary keys map { table_name->String[] }*/
	private HashMap primaryKeys = new HashMap();
	/* The foreign keys map { table_name->ForeignKey[] }*/
	private HashMap foreignKeys = new HashMap();
	
	/**
	 * The counting mode constant: default = '0'
	 * In the default mode all columns (excepting these defined as ignored) are counted.
	 */
	public static final int MODE_DEFAULT = 0;
	
	/**
	 * The counting mode constant: key as coressponding hash = '10'
	 * In this mode the primary key is omited and coresspoding hash is counted instead of 
	 * foreign key.
	 */
	public static final int MODE_KEY_AS_CORESSPONDING_HASH = 10;

	/**
	 * @param connection the database access
	 */
	public DBHash( Connection connection )
	{
		this.conn = connection;
	}
	
	/**
	 * Counts the hash code of the given table
	 * 
	 * @param tableName the name of the table to hash count
	 * @param mode the hash counting mode
	 * 
	 * @return The table state of the given table
	 * 
	 * @throws SQLException
	 */
	public TableState getTableState( String tableName, int mode )
	throws SQLException
	{
		int result = 0;
		String[] primaryKeys = null;
		ForeignKey[] foreignKeys = null;
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery( "SELECT * FROM " + tableName );
		while( rs.next() )
		{
			result += getRecordHash( mode, rs, tableName );
		}
		rs.close();
		st.close();
		return new TableState( tableName, result );
	}
	
	/**
	 * Counts the hash code of the given tables
	 * 
	 * @param tableNames the names of tables to count the hash code
	 * @param mode the hash counting mode
	 * 
	 * @return The database state for the given tables
	 * 
	 * @throws SQLException
	 */
	public DBState getTablesState( String[] tableNames, int mode )
	throws SQLException
	{
		if( mode == MODE_KEY_AS_CORESSPONDING_HASH )
		{
			DatabaseMetaData dbmd = conn.getMetaData();
			
			for( int i = 0; tableNames != null && i < tableNames.length; i++ )
			{
				primaryKeys.put( tableNames[ i ], getPrimaryKeys( tableNames[ i ], dbmd ) );
				foreignKeys.put( tableNames[ i ], getImportedKeys( tableNames[ i ], dbmd ) );
			}
		}
		DBState result = new DBState();
		for( int i = 0; tableNames != null && i < tableNames.length; i++ )
		{
			result.addTableState( getTableState( tableNames[ i ], mode ) );
		}
		
		return result;
	} 
	
	/**
	 * Counts the hash code off whole schema of the current db user.
	 * 
	 * @param mode the hash counting mode
	 * 
	 * @return The database state
	 * 
	 * @throws SQLException
	 */
	public DBState getDatabaseState( int mode )
	throws SQLException
	{
		ArrayList tables = new ArrayList();
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs  = dbmd.getTables( conn.getCatalog(), dbmd.getUserName(), "%", new String[]{ "TABLE" } );
		while( rs.next() )
		{
			tables.add( rs.getString( "TABLE_NAME" ) );
		}
		rs.close();
		return getTablesState( (String[]) tables.toArray( new String[ tables.size() ] ), mode );
	}
	
	/**
	 * Adds columns matching given pattenrs into the ignored
	 * 
	 * @param tablePattern the pattern of the table
	 * @param columnPattern the pattern of the column
	 * 
	 * @throws SQLException
	 */
	public void ignoreColumns( String tablePattern, String columnPattern )
	throws SQLException
	{
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs = dbmd.getColumns( conn.getCatalog(), dbmd.getUserName(), tablePattern, columnPattern );
		while( rs.next() )
		{
			Column c = new Column( rs.getString( "COLUMN_NAME" ), Column.GenericTypes.VARCHAR, false, false );
			ignoredColumns.add( c );
		}
		rs.close();
	}
	
	/* Returns hash code of the value at current record in given result set and given index */
	private int getColumnHash( ResultSet rs, int index )
	throws SQLException
	{
		Object o = rs.getObject( index );
		int hash = 0;
		if( o != null )
		{
			if( o instanceof oracle.sql.CLOB )
			{
				CLOB c = (CLOB) o;
				String s = c.getSubString(1, (int)c.length());
				hash = s.hashCode();
			} 
			else if( o instanceof oracle.sql.BLOB )
			{
				BLOB b = (BLOB) o;
				String s = new String( b.getBytes(1, (int) b.length() ) );
				hash = s.hashCode();
			}
			else
			{
				hash = rs.wasNull() ? 0 : o.hashCode();
			}
		}
		return hash;
	}
	
	/* Counts the hash of the current record from the given result set */
	private int getRecordHash( int mode, ResultSet rs, String tableName )
	throws SQLException
	{
		int result = 0;
		ResultSetMetaData rsmd = rs.getMetaData();
		for( int i = 1; i <= rsmd.getColumnCount(); i++ )
		{
			String column = rsmd.getColumnName( i );
			if( !isIgnored( column ) )
			{
				int hash = 0;
				if( mode == MODE_KEY_AS_CORESSPONDING_HASH )
				{
					// primary key - omit
					if( Util.inArray( (String[]) primaryKeys.get( tableName ), column ) )
					{
						continue;
					}
					else if( getForeignKey( (ForeignKey[]) foreignKeys.get( tableName ), column ) != null )
					{
						ForeignKey key = getForeignKey( (ForeignKey[]) foreignKeys.get( tableName ), column );
						Statement st = conn.createStatement();
						ResultSet internalRs = st.executeQuery( "SELECT * FROM " + key.getPrimaryKeyTableName() + " WHERE " + key.getPrimaryKeyColumnName() + " = " + rs.getObject( i ) );
						if( internalRs.next() )
						{
							hash = getRecordHash( mode, internalRs, key.getPrimaryKeyTableName() );
						}
						internalRs.close();
						st.close(); 
					}
					else
					{
						hash = getColumnHash( rs, i );
					}
					result += ( column + hash ).hashCode(); 
				}
			}
		}
		return result;
	}
	
	/* Returns the primary keys of the given table */
	private String[] getPrimaryKeys( String tableName, DatabaseMetaData dbmd ) 
	throws SQLException
	{
		String[] result = null;
		ResultSet rs = dbmd.getPrimaryKeys( conn.getCatalog(), dbmd.getUserName(), tableName );
		ResultSetMetaData rsmd = rs.getMetaData();
		ArrayList keys = new ArrayList();
		while(rs.next())
		{
			String columnName = rs.getString( "COLUMN_NAME" );
			keys.add( columnName );
		}
		rs.close();
		return (String[]) keys.toArray( new String[ keys.size() ] );
	}
	
	/* Returns the table imported keys */
	private ForeignKey[] getImportedKeys( String tableName, DatabaseMetaData dbmd )
	throws SQLException
	{
		ResultSet rs = dbmd.getImportedKeys( conn.getCatalog(), dbmd.getUserName(), tableName );
		ArrayList keys = new ArrayList();
		while(rs.next())
		{
			String pktn = rs.getString( "PKTABLE_NAME" );
			String pkcn = rs.getString( "PKCOLUMN_NAME" );
			String fktn = rs.getString( "FKTABLE_NAME" );
			String fkcn = rs.getString( "FKCOLUMN_NAME" );
			keys.add( new ForeignKey( pktn, pkcn, fktn, fkcn ) );
		}
		rs.close();
		
		return (ForeignKey[]) keys.toArray( new ForeignKey[ keys.size() ] );
	}

	/* Searches for the foreign key in the given table by name */
	private ForeignKey getForeignKey( ForeignKey[] foreignKeys, String columnName )
	{
		for( int i = 0; foreignKeys != null && i < foreignKeys.length; i++ )
		{
			if( foreignKeys[ i ].getForeignKeyColumnName().equals( columnName ) )
			{
				return foreignKeys[ i ];
			}
		}
		return null;
	}	
	
	/* Checks if the given column of the given table is ignored */
	private boolean isIgnored( String column )
	{
		for( int i = 0; ignoredColumns != null && i < ignoredColumns.size(); i++ )
		{
			Column col = (Column) ignoredColumns.get( i );
			if( col.getName().equals( column )  )
			{
				return true;
			}
		}
		return false; 
	}
	
}
